# coding=utf-8
# Written by YuelinZhang
import pymssql
import decimal  # for pyinstaller
import os


class SQLServer(object):
    def __init__(self):
        self.conn = None
        self.connstatus = False
        self.cursor = None

    def login(self, ip, username, password):
        """
        login, create conn
        :param ip: IP of SQL Server
        :param username: Windows username
        :param password: Windows login password
        :return: 0-illegal input, 1-successfully login, 2-password or username wrong, 3-connection error
        """
        try:
            self.conn = pymssql.connect(host=ip, user=username, password=password, charset="utf8")
            if self.conn:
                self.cursor = self.conn.cursor()
                self.conn.autocommit(True)
                self.connstatus = True
                return 1
            else:
                self.connstatus = False
                return 3
        except pymssql.OperationalError:
            self.connstatus = False
            return 2
        except pymssql.InterfaceError:  # Connection to the database failed for an unknown reason.
            self.connstatus = False
            return 3

    def create_database(self):
        """
        create database if it is not exist
        :return: 0-Connection lost, 1-Success, 2-OperationalError, 3-Success(database exist)
        """
        if self.connstatus:
            workdir = os.getcwd()
            try:  # try1: try to create the database
                self.cursor.execute(
                    'CREATE DATABASE TESTZ ON (NAME=\'TESTZ_Data\', FILENAME=\'{}\\TESTZ.mdf\', SIZE=5 MB, MAXSIZE=50 MB, FILEGROWTH=5%)'.format(
                        workdir),
                    'LOG ON (NAME=\'TESTZ_Log\',FILENAME=\'{}\\TESTZ_Log.ldf\',SIZE=1 MB,MAXSIZE=5 MB,FILEGROWTH=1 MB)'.format(
                        workdir))
            except pymssql.OperationalError:  # except1: database and data already exist
                return 3
                pass
            else:  # else1: database and data not exist
                try:  # try2: create table, trigger, constraint
                    # Create 3 tables
                    # The first operation must follow the order: D -> E -> S (to follow the ref restriction)
                    self.cursor.execute(
                        'USE TESTZ CREATE TABLE Departments ( DepartmentID char(3) NOT NULL PRIMARY KEY, '
                        'DepartmentName nvarchar(10) NOT NULL, Note nvarchar(30) NULL)')
                    self.cursor.execute('USE TESTZ CREATE TABLE Employees ( EmployeeID char(6) NOT NULL PRIMARY KEY, '
                                        'Name nvarchar(10) NOT NULL, Sex nvarchar(1) NOT NULL, '
                                        'WorkYear tinyint NULL, SalaryDue float NULL, SalaryReal float NULL, '
                                        'DepartmentID char(3) NOT NULL FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID), '
                                        'Position nvarchar(8) NOT NULL, Rank nvarchar(8) NOT NULL)')
                    self.cursor.execute('USE TESTZ CREATE TABLE Salary ( ID int IDENTITY(1,1) PRIMARY KEY, '
                                        'EmployeeID char(6) NOT NULL FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID), '
                                        'Amount float NOT NULL, Note nvarchar(30) NULL)')
                    # Create trigger1
                    self.cursor.execute('CREATE TRIGGER EmployeesSalary ON dbo.Salary FOR INSERT, UPDATE AS BEGIN '
                                        'UPDATE Employees SET SalaryDue=(select SUM(Amount) '
                                        'from Salary where (EmployeeID=(select EmployeeID from inserted)) and Note=\'应发\') '
                                        'where EmployeeID=(select EmployeeID from inserted) '
                                        'UPDATE Employees SET SalaryReal=(select SUM(Amount) '
                                        'from Salary where EmployeeID=(select EmployeeID from inserted)) '
                                        'where EmployeeID=(select EmployeeID from inserted) '
                                        'END')
                    # Create trigger2
                    self.cursor.execute('CREATE TRIGGER EmployeesSalary2 ON dbo.Salary FOR DELETE AS BEGIN '
                                        'UPDATE Employees SET SalaryDue=(select SUM(Amount) '
                                        'from Salary where (EmployeeID=(select EmployeeID from deleted)) and Note=\'应发\') '
                                        'where EmployeeID=(select EmployeeID from deleted) '
                                        'UPDATE Employees SET SalaryReal=(select SUM(Amount) '
                                        'from Salary where EmployeeID=(select EmployeeID from deleted)) '
                                        'where EmployeeID=(select EmployeeID from deleted) '
                                        'END')
                except pymssql.OperationalError:  # except2: return the error info
                    return 2
                try:  # try3: insert the initial data into the database
                    self._init_data()
                except pymssql.OperationalError:  # except3: return the error info
                    return 2
                return 1
        else:
            return 0

    def _init_data(self):
        """
        generate a set of records for evaluation
        :return:
        """
        # first insert must follow the order: D -> E -> S (to follow the ref restriction)
        self.cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'001\', \'销售部\', \'\')')
        self.cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'002\', \'研发部\', \'\')')
        self.cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'003\', \'财务部\', \'\')')
        self.cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'004\', \'人力资源部\', \'\')')
        self.cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'005\', \'公关部\', \'\')')
        self.cursor.execute(
            'USE TESTZ INSERT INTO Employees VALUES(\'000001\',\'张三\',\'男\',8,0,0,\'001\',\'职员\',\'中级员工\')')  # 创建时两种Salary都为0，值通过触发器修改
        self.cursor.execute(
            'USE TESTZ INSERT INTO Employees VALUES(\'000002\',\'李四\',\'女\',10,0,0,\'001\',\'经理\',\'高级员工\')')
        self.cursor.execute(
            'USE TESTZ INSERT INTO Employees VALUES(\'000003\',\'王五\',\'男\',2,0,0,\'003\',\'职员\',\'初级员工\')')
        self.cursor.execute(
            'USE TESTZ INSERT INTO Employees VALUES(\'000004\',\'刘六\',\'女\',5,0,0,\'005\',\'职员\',\'中级员工\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', 5000, \'应发\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -200, \'应扣\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -200, \'应扣\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -100, \'应扣\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000002\', 9000, \'应发\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000002\', -100, \'应扣\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000003\', 4000, \'应发\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000003\', -150, \'应扣\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000004\', 4000, \'应发\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000004\', 1000, \'应发\')')
        self.cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000004\', -50, \'应扣\')')

    def select(self, table, key='', keyname=None, out=None):
        """
        select record from database
        :param out: column of output, default is *
        :param key: value of key, default is ''
        :param keyname: column name of key
        :param table: choose which table to select, 'E' or 'S' or 'D'
        :return: 0-connection lost, [...]-success, 2-operational error
        """
        if self.connstatus:
            if table is 'E':
                table = 'Employees'
            elif table is 'S':
                table = 'Salary'
            elif table is 'D':
                table = 'Departments'
            else:
                return []
            out = '*' if out is None else out
            try:
                if keyname is None:
                    self.cursor.execute('USE TESTZ SELECT {} FROM {}'.format(out, table))
                else:
                    self.cursor.execute('USE TESTZ SELECT {} FROM {} WHERE {}={}'.format(out, table, keyname, key))
            except pymssql.OperationalError:
                return 2
            else:
                return self.cursor.fetchall()
        else:
            return []

    def insert(self, table, data):
        """
        insert a record into database
        :param table: choose which table to select, 'E' or 'S' or 'D'
        :param data: []list, data to insert
        :return: 0-connection lost, 1-success, 2-illegal input, 3-illegal format of input, 4-trigger/constraint error,
        """
        if self.connstatus:
            if table is 'E':
                if len(data) != 9:
                    return 2
                table = 'Employees'
                data_str = '\'{}\',\'{}\',\'{}\',{},{},{},\'{}\',\'{}\',\'{}\''.format(
                    data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8])
            elif table is 'S':
                if len(data) != 3:
                    return 2
                table = 'Salary'
                data_str = '\'{}\', {}, \'{}\''.format(data[0], data[1], data[2])
            elif table is 'D':
                if len(data) != 3:
                    return 2
                table = 'Departments'
                data_str = '\'{}\', \'{}\', \'{}\''.format(data[0], data[1], data[2])
            else:
                return 2
            try:
                self.cursor.execute('INSERT INTO {} VALUES({})'.format(table, data_str))
            except pymssql.IntegrityError as _e:
                if str(_e).find('PRIMARY') != -1:
                    return 4
                elif str(_e).find('REFERENCE') != -1:
                    return 5
                elif str(_e).find('FOREIGN') != -1:
                    return 6
                else:
                    return 3
            except pymssql.OperationalError:
                return 3  # 其他错误（参数格式错误:NULL错误、长度错误、类型错误等）
            else:
                return 1
        else:
            return 0

    def delete(self, table, key, keyname=None):
        """
        delete record in database
        :param keyname: column name of key, default is primary key
        :param table: 'E' or 'S' or 'D'
        :param key: value of key  —— ？？？输入nvarchar应写作：'\'xxx\''？？？
        :return: 0-connection lost, 1-success, 2-operational error, 3-constraint error
        """
        if self.connstatus:
            if table is 'E':
                self.cursor.execute('ALTER TABLE Salary DISABLE TRIGGER all')  # 先禁用触发器
                table = 'Employees'
                keyname = 'EmployeeID' if keyname is None else keyname
            elif table is 'S':
                table = 'Salary'
                keyname = 'ID' if keyname is None else keyname
            elif table is 'D':
                self.cursor.execute('ALTER TABLE Salary DISABLE TRIGGER all')  # 先禁用触发器
                table = 'Departments'
                keyname = 'DepartmentID' if keyname is None else keyname
            else:
                return 2
            try:
                self.cursor.execute('DELETE FROM {} WHERE {}={}'.format(table, keyname, key))
            except pymssql.OperationalError:
                self.cursor.execute('ALTER TABLE Salary ENABLE TRIGGER all')
                return 2
            except pymssql.IntegrityError:
                self.cursor.execute('ALTER TABLE Salary ENABLE TRIGGER all')
                return 3
            else:
                self.cursor.execute('ALTER TABLE Salary ENABLE TRIGGER all')
                return 1
        else:
            return 0

    def delete_database(self):
        """
        delete the whole database
        :return:
        """
        if self.connstatus:
            try:
                self.cursor.execute('ALTER database TESTZ set single_user with rollback immediate')
                self.cursor.execute('USE MASTER DROP DATABASE TESTZ')
            except pymssql.OperationalError:
                return 2
            else:
                return 1
        else:
            return 0

    def check_connection(self):
        """
        check connection
        :return: 0-connection lost, 1-connection success
        """
        if self.connstatus:
            try:
                self.cursor.execute('USE TESTZ SELECT * FROM Employees')
                self.cursor.execute('USE TESTZ SELECT * FROM Salary')
                self.cursor.execute('USE TESTZ SELECT * FROM Departments')
            except:
                self.connstatus = False
                return 0
            else:
                return 1
        else:
            return 0


if __name__ == '__main__':
    """
    For test during developing, DO NOT run this file.
    用于开发时的测试, 不要运行这个文件。
    """
    # 删除数据库
    # dir = os.getcwd()
    # conn = pymssql.connect(host='127.0.0.1', user='DESKTOP-SFPF05V\\22394',
    #                        password='20001014yy', charset="utf8")
    # cursor = conn.cursor()
    # conn.autocommit(True)
    # cursor.execute('alter database TESTZ set single_user with rollback immediate')
    # cursor.execute('USE MASTER DROP DATABASE TESTZ')

    # 创建数据库并删除
    dir = os.getcwd()
    conn = pymssql.connect(host='127.0.0.1', user='DESKTOP-SFPF05V\\22394',
                           password='20001014yy', charset="utf8")
    print(conn)
    cursor = conn.cursor()
    conn.autocommit(True)
    cursor.execute(
        'CREATE DATABASE TESTZ ON (NAME=\'TESTZ_Data\', FILENAME=\'{}\\TESTZ.mdf\', SIZE=5 MB, MAXSIZE=50 MB, FILEGROWTH=5%) LOG ON (NAME=\'TESTZ_Log\',FILENAME=\'{}\\TESTZ_Log.ldf\',SIZE=1 MB,MAXSIZE=5 MB,FILEGROWTH=1 MB)'.format(
            dir, dir))
    cursor.execute('USE TESTZ CREATE TABLE Departments ( DepartmentID char(3) NOT NULL PRIMARY KEY, '
                   'DepartmentName nvarchar(10) NOT NULL, Note nvarchar(30) NULL)')
    cursor.execute('USE TESTZ CREATE TABLE Employees ( EmployeeID char(6) NOT NULL PRIMARY KEY, '
                   'Name nvarchar(10) NOT NULL, Sex nvarchar(1) NOT NULL, '
                   'WorkYear tinyint NULL, SalaryDue float NOT NULL, SalaryReal float NOT NULL, '
                   'DepartmentID char(3) NOT NULL FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID), '
                   'Position nvarchar(8) NOT NULL, Rank nvarchar(8) NOT NULL)')
    cursor.execute('USE TESTZ CREATE TABLE Salary ( ID int IDENTITY(1,1) PRIMARY KEY, '
                   'EmployeeID char(6) NOT NULL FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID), '
                   'Amount float NOT NULL, Note nvarchar(30) NULL)')
    cursor.execute('CREATE TRIGGER EmployeesSalary ON dbo.Salary FOR INSERT, UPDATE AS BEGIN '
                   'UPDATE Employees SET SalaryDue=(select SUM(Amount) '
                   'from Salary where (EmployeeID=(select EmployeeID from inserted)) and Note=\'应发\') '
                   'where EmployeeID=(select EmployeeID from inserted) '
                   'UPDATE Employees SET SalaryReal=(select SUM(Amount) '
                   'from Salary where EmployeeID=(select EmployeeID from inserted)) '
                   'where EmployeeID=(select EmployeeID from inserted) '
                   'END')
    cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'001\', \'销售部\', \'\')')
    cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'002\', \'研发部\', \'\')')
    cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'003\', \'财务部\', \'\')')
    cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'004\', \'人力资源部\', \'\')')
    cursor.execute('USE TESTZ INSERT INTO Departments VALUES(\'005\', \'公关部\', \'\')')
    cursor.execute(
        'USE TESTZ INSERT INTO Employees VALUES(\'000001\',\'张三\',1,8,0,0,\'001\',\'职员\',\'中级员工\')')
    cursor.execute(
        'USE TESTZ INSERT INTO Employees VALUES(\'000002\',\'李四\',0,10,0,0,\'001\',\'经理\',\'高级员工\')')
    cursor.execute(
        'USE TESTZ INSERT INTO Employees VALUES(\'000003\',\'王五\',1,2,0,0,\'003\',\'职员\',\'初级员工\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', 5000, \'应发\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -200, \'应扣\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -200, \'应扣\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000001\', -100, \'应扣\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000002\', 9000, \'应发\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000002\', -100, \'应扣\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000003\', 4000, \'应发\')')
    cursor.execute('USE TESTZ INSERT INTO Salary VALUES(\'000003\', -150, \'应扣\')')

    cursor.execute('CREATE VIEW SalaryView (EmployeeID, SalaryDue, SalaryDeduct, SalaryReal) AS '
                   'SELECT Salary.EmployeeID, SalaryDue, SalaryReal-SalaryDue, SalaryReal '
                   'From Salary, Employees WHERE Employees.EmployeeID = Salary.EmployeeID')

    cursor.execute('SELECT DISTINCT EmployeeID FROM Employees WHERE DepartmentID=001')
    cursor.execute('SELECT * FROM SalaryView')
    print(cursor.fetchall())
    cursor.execute('DROP TABLE Salary')
    cursor.execute('alter database TESTZ set single_user with rollback immediate')
    cursor.execute('USE MASTER DROP DATABASE TESTZ')

    # 获取当前主机名和用户名
    # host_name = socket.gethostname()
    # user_name = getpass.getuser()
    # print(host_name, user_name)
